Considerations for Bind Variables in Table

After you assign a value set to a flexfield, you can use bind variables in the WHERE clause.

These bind variables refer to flexfield elements:

:{SEGMENT.}

:{CONTEXT.;SEGMENT.}

:{VALUESET.}

:{FLEXFIELD.}

:{PARAMETER.}

:{SEGMENT.}

This bind variable refers to the ID or value of a segment where identifies the segment. Where referring to the ID, the value set is ID-validated. Where referring to the value, the value set isn't ID-validated. The data type of the bind value is the same as the data type of the segment's column.

For both descriptive and extensible flexfields, the segment must be in the same context as the source segment. The source segment contains the WHERE clause. For descriptive flexfields, if the segment is global, then the source segment must be global.

The segment must have a sequence number that's less than the sequence number of the target segment with this bind variable. A matching segment must exist in the current flexfield context.

This bind variable is useful when the set of valid values depends on the value in another segment. For example, the values to select from a CITIES table might depend upon the selected country. If SEGMENT1 contains the country value, then the WHERE clause for the CITIES table might be = :{SEGMENT.SEGMENT1}.

:{CONTEXT.;SEGMENT.}

This bind variable, which is valid only for extensible flexfields, refers to the ID (if the value set is ID-validated) or value (if not ID-validated) of a segment that's in a different context than the target segment (the segment with the WHERE clause).

The identifies the context and must be in the same category or in an ancestor category. It can't be a multiple-row context.

The identifies the segment. The data type of the bind value is the same as the data type of the segment's column.

The framework of extensible flexfields doesn't perform any additional validation related to mismatched values for segments defined with cross context bind parameters. Administrators must populate the correct pair of segment values.

This bind variable is useful when the set of valid values depends on the value of a segment in another context. For example, the values to select from a CERTIFICATION table for a segment in the Compliance and Certification context might depend on the value of the country segment in the Manufacturing context.

:{VALUESET.}

This bind variable refers to the ID (if the value set is ID-validated) or value (if not ID-validated) of the segment that's assigned to the value set that's identified by the value_set_code. The data type of the bind value is the same as the data type of the segment's column.

The segment must have a sequence number that's less than the sequence number of the segment with this bind variable. If more than one segment is assigned to the value set, the closest prior matching segment will be used to resolve the bind expression. A matching segment must exist in the current flexfield context.

This bind variable is useful when the set of valid values depends on the value in another segment and that segment code can vary, such as when the value set is used for more than one context or flexfield. For example, the values to select from a CITIES table might depend upon the selected country. If the value set for the segment that contains the country value is COUNTRIES, then the WHERE clause for the CITIES table might be = :{VALUESET.COUNTRIES}.

:{FLEXFIELD.}

This bind variable refers to an internal code of the flexfield in which the value set is used, or to a validation date. The internal_code must be one of the following:

APPLICATION_ID - the application ID of the flexfield in which this value set is used. The data type of APPLICATION_ID and its resulting bind value is NUMBER.

DESCRIPTIVE_FLEXFIELD_CODE - the identifying code of the flexfield in which this value set is used. The data type of DESCRIPTIVE_FLEXFIELD_CODE and its resulting bind value is VARCHAR2. Note that you use this string for both descriptive and extensible flexfields.

CONTEXT_CODE - the context code of the flexfield context in which this value set is used. The data type of CONTEXT_CODE and its resulting bind value is VARCHAR2.

SEGMENT_CODE - the identifying code of the flexfield segment in which this value set is used. The data type of SEGMENT_CODE and its resulting bind value is VARCHAR2.

VALIDATION_DATE - the current database date. The data type of VALIDATION_DATE and its resulting bind value is DATE.

:{PARAMETER.}

This bind variable refers to the value of a flexfield parameter where parameter_code identifies the parameter. The data type of the resulting bind value is the same as the parameter's data type.